package ws.jonas.hoarder.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import ws.jonas.common.config.Config;
import ws.jonas.common.log.Logger;

public class DB {
	private static final Logger log = Logger.getInstance(DB.class);
	
	private static final String SELECT_PRODUCT_ID = "SELECT id FROM products WHERE prod_nr = ? AND volume = ?";
	private static final String SELECT_STORE_ID = "SELECT id FROM stores WHERE city = ? AND address = ? AND county = ?";
	private static final String INSERT_PRODUCT = "INSERT INTO products(prod_nr, volume, name) VALUES(?, ?, ?)";
	private static final String INSERT_STORE = "INSERT INTO stores(city, address, county) VALUES(?, ?, ?)";
	private static final String INSERT_STOCK = "INSERT INTO stocks(prod_id, store_id, quantity, insert_timestamp)" +
			"VALUES((SELECT id FROM products WHERE prod_nr = ? AND volume = ?), (SELECT id FROM stores WHERE city = ? AND address = ? and county = ?), ?, NOW())";

	private PreparedStatement selectProductId;
	private PreparedStatement selectStoreId;
	private PreparedStatement insertProductPs;
	private PreparedStatement insertStorePs;
	private PreparedStatement insertStockPs;
	private Connection conn;
	
	public DB() throws SQLException {
		Properties props = new Properties();
		props.put("user", Config.get(Config.DB_USER_KEY));
		props.put("password", Config.get(Config.DB_PASSWORD_KEY));
		props.put("characterEncoding", "UTF-8");
		try {
			conn = DriverManager.getConnection("jdbc:" + Config.get(Config.DB_HOST_KEY), props);
		} catch (SQLException e) {
			log.severe("Could not set up database connection, shutting down: " + e.getMessage());
			throw(e);
		}

		try {
			selectProductId = conn.prepareStatement(SELECT_PRODUCT_ID);
			selectStoreId = conn.prepareStatement(SELECT_STORE_ID);
			insertProductPs = conn.prepareStatement(INSERT_PRODUCT);
			insertStorePs = conn.prepareStatement(INSERT_STORE);
			insertStockPs = conn.prepareStatement(INSERT_STOCK);
		} catch (SQLException e) {
			log.severe("Could not set up database queries, shutting down");
			log.severe(e);
			throw(e);
		}
	}
	
	/**
	 * @return true if any modification was done to the database. 
	 * @throws SQLException if any SQL error occurred.
	 */
	public boolean addProductIfNotExists(int prodnr, String name, String volume) throws SQLException {
		selectProductId.setInt(1, prodnr);
		selectProductId.setString(2, volume);
		ResultSet selectRs = selectProductId.executeQuery();
		if (selectRs.first()) {
			return false;
		} else {
			insertProduct(prodnr, name, volume);
			return true;
		}
	}
	
	/**
	 * @return true if any modification was done to the database.
	 * @throws SQLException if any SQL error occurred.
	 */
	public boolean addStoreIfNotExists(String city, String address, String county) throws SQLException {
		selectStoreId.setString(1, city);
		selectStoreId.setString(2, address);
		selectStoreId.setString(3, county);
		ResultSet selectRs = selectStoreId.executeQuery();
		if (selectRs.first()) {
			return false;
		} else {
			insertStore(address, city, county);
			return true;
		}
	}
	
	/**
	 * @return true.
	 * @throws SQLException if any SQL error occurred.
	 */
	public boolean addStock(int prodnr, String name, String volume, String county, String city, String address, int qty) throws SQLException {
		insertStock(prodnr, volume, address, city, county, qty);
		return true;
	}

	private void insertProduct(int prodnr, String name, String volume) throws SQLException {
		insertProductPs.setInt(1, prodnr);
		insertProductPs.setString(2, volume);
		insertProductPs.setString(3, name);
		insertProductPs.executeUpdate();
	}

	private void insertStore(String address, String city, String county) throws SQLException {
		insertStorePs.setString(1, city);
		insertStorePs.setString(2, address);
		insertStorePs.setString(3, county);
		insertStorePs.executeUpdate();
	}

	private void insertStock(int prodnr, String volume, String address, String city, String county, int quantity) throws SQLException {
		insertStockPs.setInt(1, prodnr);
		insertStockPs.setString(2, volume);
		insertStockPs.setString(3, city);
		insertStockPs.setString(4, address);
		insertStockPs.setString(5, county);
		insertStockPs.setInt(6, quantity);
		insertStockPs.executeUpdate();
	}
}
